CREATE OR REPLACE EDITIONABLE PROCEDURE "SCOTT"."P_SELECT_STOCK" (stockDate in varchar2,percentage in number,arrayStockCode OUT TYPE_ARRAY) as
begin
    ------------------------------------------ standard declare section ---------------------------------------------------
    declare
    -- 表示stock_code
    stockCode varchar2(10);
    -- 表示某只股票历史上的最高收盘价
    maxStockClose number;
    -- 表示当前股票某一日的收盘价
    currentStockClose number;
    -- 表示当前股票某一日的120均线价格
    currentStockOneHundredTwenty number;
    -- 用于计算某只股票的120均线是否在20个交易日内不是单调递减的
    num number;
    -- 定义一个字符型数据的数组，用于存储被选中的股票的代码。TYPE_ARRAY的定义在Type目录中。
    arrayStockCode type_array:=type_array();
    -- 从stock_code表中选出所有股票
    cursor allStockCode is select distinct t.stock_code from stock_moving_average t where t.stock_date=to_date(stockDate,'yyyy-mm-dd');
    /*cursor allStockCode is select distinct t.stock_code from stock_moving_average t
            where exists (
                  select * from stock_moving_average d where d.stock_date=to_date(stockDate,'yyyy-mm-dd')
            );*/
    -- define cursor section.返回全部在某一日均线成多头排列的股票
    cursor bullRankStockCode is select distinct * from stock_moving_average t
            where t.stock_close>=t.five and t.stock_close>=t.ten and t.stock_close>=t.twenty and t.stock_close>=t.sixty and t.stock_close>=t.one_hundred_twenty
            and t.five>=t.ten and t.five>=t.twenty and t.five>=t.sixty and t.five>=t.one_hundred_twenty
            and t.ten>=t.twenty and t.ten>=t.sixty and t.ten>=t.one_hundred_twenty
            and t.twenty>=t.sixty and t.twenty>=t.one_hundred_twenty
            and t.sixty>=t.one_hundred_twenty
            and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
    -- define cursor section.返回全部在某一支股票在某一日之后（包括某一日）的20个交易日内的交易记录
    cursor topTwentyStockClose is select * from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd')) b where rownum<=20 order by b.stock_date desc;
    ------------------------------------------ standard declare section ---------------------------------------------------

    begin
        dbms_output.put_line('sssssssss'||'  :  ');
        --------------------------------- standard for loop section --------------------------------------------------------
        for i in bullRankStockCode loop
            stockCode:=i.stock_code;
            -- 查询某只股票历史上的最高收盘价
            select max(t.stock_close) into maxStockClose from stock_moving_average t where t.stock_code=stockCode;
            -- 查询某只股票在某一日的收盘价格和120均线价格
            select t.stock_close,t.one_hundred_twenty into currentStockClose,currentStockOneHundredTwenty from stock_moving_average t where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
            /*select t.stock_close,t.one_hundred_twenty into currentStockClose,currentStockOneHundredTwenty from stock_moving_average t where exists (
                  select * from stock_moving_average d where d.stock_date=to_date(stockDate,'yyyy-mm-dd') and d.stock_code=stockCode
            ) and t.stock_date=to_date(stockDate,'yyyy-mm-dd') and t.stock_code=stockCode;*/

            num:=0;
            -- 计算某只股票的某一日的收盘价格是否是其历史最高价的百分子：1-percentage
            if (maxStockClose-currentStockClose)/maxStockClose>percentage then
                for j in topTwentyStockClose loop
                    -- 判断某只股票的120均线是否在20个交易日内不是单调递减的
                    if currentStockOneHundredTwenty<j.one_hundred_twenty then
                        exit;
                    end if;

                    currentStockOneHundredTwenty:=j.one_hundred_twenty;
                    arrayStockCode.extend;
                    num:=1+num;

                    if num=20 then
                        arrayStockCode(arrayStockCode.count):=j.stock_code;
                        dbms_output.put_line('j.stock_code'||'  :  '||j.stock_code);
                    end if;
                end loop;
            end if;
        end loop;
    end;
end;